﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Xml;

namespace DevTeam
{
    public static class Extension
    {
        public static string GetCreateTableSql(DataTable table)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder alterSql = new StringBuilder();

            sql.AppendFormat("CREATE TABLE [{0}] (", table.TableName);

            for (int i = 0; i < table.Columns.Count; i++)
            {
                bool isNumeric = false;
                bool usesColumnDefault = true;

                sql.AppendFormat("\n\t[{0}]", table.Columns[i].ColumnName);

                switch (table.Columns[i].DataType.ToString().ToUpper())
                {
                    case "SYSTEM.INT16":
                        sql.Append(" smallint");
                        isNumeric = true;
                        break;
                    case "SYSTEM.INT32":
                        sql.Append(" int");
                        isNumeric = true;
                        break;
                    case "SYSTEM.INT64":
                        sql.Append(" bigint");
                        isNumeric = true;
                        break;
                    case "SYSTEM.DATETIME":
                        sql.Append(" datetime");
                        usesColumnDefault = false;
                        break;
                    case "SYSTEM.STRING":
                        sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
                        break;
                    case "SYSTEM.SINGLE":
                        sql.Append(" single");
                        isNumeric = true;
                        break;
                    case "SYSTEM.DOUBLE":
                        sql.Append(" double");
                        isNumeric = true;
                        break;
                    case "SYSTEM.DECIMAL":
                        sql.AppendFormat(" decimal(18, 6)");
                        isNumeric = true;
                        break;
                    default:
                        sql.AppendFormat(" nvarchar({0})", table.Columns[i].MaxLength);
                        break;
                }

                if (table.Columns[i].AutoIncrement)
                {
                    sql.AppendFormat(" IDENTITY({0},{1})",
                        table.Columns[i].AutoIncrementSeed,
                        table.Columns[i].AutoIncrementStep);
                }
                else
                {
                    // DataColumns will add a blank DefaultValue for any AutoIncrement column. 
                    // We only want to create an ALTER statement for those columns that are not set to AutoIncrement. 
                    if (table.Columns[i].DefaultValue != null)
                    {
                        if (usesColumnDefault)
                        {
                            if (isNumeric)
                            {
                                alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ({2}) FOR [{1}];",
                                    table.TableName,
                                    table.Columns[i].ColumnName,
                                    table.Columns[i].DefaultValue);
                            }
                            else
                            {
                                alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ('{2}') FOR [{1}];",
                                    table.TableName,
                                    table.Columns[i].ColumnName,
                                    table.Columns[i].DefaultValue);
                            }
                        }
                        else
                        {
                            // Default values on Date columns, e.g., "DateTime.Now" will not translate to SQL.
                            // This inspects the caption for a simple XML string to see if there is a SQL compliant default value, e.g., "GETDATE()".
                            try
                            {
                                System.Xml.XmlDocument xml = new System.Xml.XmlDocument();

                                xml.LoadXml(table.Columns[i].Caption);

                                alterSql.AppendFormat("\nALTER TABLE {0} ADD CONSTRAINT [DF_{0}_{1}]  DEFAULT ({2}) FOR [{1}];",
                                    table.TableName,
                                    table.Columns[i].ColumnName,
                                    xml.GetElementsByTagName("defaultValue")[0].InnerText);
                            }
                            catch
                            {
                                // Handle
                            }
                        }
                    }
                }

                if (!table.Columns[i].AllowDBNull)
                {
                    sql.Append(" NOT NULL");
                }

                sql.Append(",");
            }

            if (table.PrimaryKey.Length > 0)
            {
                StringBuilder primaryKeySql = new StringBuilder();

                primaryKeySql.AppendFormat("\n\tCONSTRAINT PK_{0} PRIMARY KEY (", table.TableName);

                for (int i = 0; i < table.PrimaryKey.Length; i++)
                {
                    primaryKeySql.AppendFormat("{0},", table.PrimaryKey[i].ColumnName);
                }

                primaryKeySql.Remove(primaryKeySql.Length - 1, 1);
                primaryKeySql.Append(")");

                sql.Append(primaryKeySql);
            }
            else
            {
                sql.Remove(sql.Length - 1, 1);
            }

            sql.AppendFormat("\n);\n{0}", alterSql.ToString());

            return sql.ToString();
        }

        public static string BuildCreateTableScript(DataTable Table)
        {
            //if (!Helper.IsValidDatatable(Table, IgnoreZeroRows: true))
            //    return string.Empty;

            StringBuilder result = new StringBuilder();
            result.AppendFormat("CREATE TABLE [{1}] ({0}   ", Environment.NewLine, Table.TableName);

            bool FirstTime = true;
            foreach (DataColumn column in Table.Columns.OfType<DataColumn>())
            {
                if (FirstTime) FirstTime = false;
                else
                    result.Append("   ,");

                result.AppendFormat("[{0}] {1} {2} {3}",
                    column.ColumnName, // 0
                    GetSQLTypeAsString(column.DataType), // 1
                    column.AllowDBNull ? "NULL" : "NOT NULL", // 2
                    Environment.NewLine // 3
                );
            }
            result.AppendFormat(") ON [PRIMARY]{0}GO{0}{0}", Environment.NewLine);

            // Build an ALTER TABLE script that adds keys to a table that already exists.
            if (Table.PrimaryKey.Length > 0)
                result.Append(BuildKeysScript(Table));

            return result.ToString();
        }

        /// <summary>
        /// Builds an ALTER TABLE script that adds a primary or composite key to a table that already exists.
        /// </summary>
        private static string BuildKeysScript(DataTable Table)
        {
            // Already checked by public method CreateTable. Un-comment if making the method public
            // if (Helper.IsValidDatatable(Table, IgnoreZeroRows: true)) return string.Empty;
            if (Table.PrimaryKey.Length < 1) return string.Empty;

            StringBuilder result = new StringBuilder();

            if (Table.PrimaryKey.Length == 1)
                result.AppendFormat("ALTER TABLE {1}{0} ADD PRIMARY KEY ({2}){0} GO{0}{0}", Environment.NewLine, Table.TableName, Table.PrimaryKey[0].ColumnName);
            else
            {
                List<string> compositeKeys = Table.PrimaryKey.OfType<DataColumn>().Select(dc => dc.ColumnName).ToList();
                string keyName = compositeKeys.Aggregate((a, b) => a + b);
                string keys = compositeKeys.Aggregate((a, b) => string.Format("{0}, {1}", a, b));
                result.AppendFormat("ALTER TABLE {1}{0} ADD CONSTRAINT pk_{3} PRIMARY KEY ({2}){0} GO{0}{0}", Environment.NewLine, Table.TableName, keys, keyName);
            }

            return result.ToString();
        }

        /// <summary>
        /// Returns the SQL data type equivalent, as a string for use in SQL script generation methods.
        /// </summary>
        private static string GetSQLTypeAsString(Type DataType)
        {
            switch (DataType.Name)
            {
                case "Boolean": return "[bit]";
                case "Char": return "[char]";
                case "SByte": return "[tinyint]";
                case "Int16": return "[smallint]";
                case "Int32": return "[int]";
                case "Int64": return "[bigint]";
                case "Byte": return "[tinyint] UNSIGNED";
                case "UInt16": return "[smallint] UNSIGNED";
                case "UInt32": return "[int] UNSIGNED";
                case "UInt64": return "[bigint] UNSIGNED";
                case "Single": return "[float]";
                case "Double": return "[double]";
                case "Decimal": return "[decimal]";
                case "DateTime": return "[datetime]";
                case "Guid": return "[uniqueidentifier]";
                case "Object": return "[variant]";
                case "String": return "[nvarchar](250)";
                default: return "[nvarchar](MAX)";
            }
        }




        public static void SetPK(this DataTable dataTable, string columnName)
        {
            dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns[columnName] };
        }

        public static void SetPK2(this DataTable dataTable, string columnName)
        {
            dataTable.AddAutoIncrementalColumn(columnName);
            dataTable.SetPK(columnName);
        }

        public static void AddAutoIncrementalColumn(this DataTable dataTable, string columnName, int Seed = -1, int Step = -1)
        {
            if (!dataTable.Columns.Contains(columnName))
                dataTable.Columns.Add(columnName, typeof(int));

            dataTable.Columns[columnName].AutoIncrement = true;
            dataTable.Columns[columnName].AutoIncrementSeed = Seed;
            dataTable.Columns[columnName].AutoIncrementStep = Step;
        }

        public static void BinarySerialize(this DataSet ds, string path)
        {
            ds.RemotingFormat = SerializationFormat.Binary;
            FileStream fs = new FileStream(path, FileMode.Create);
            BinaryFormatter fmt = new BinaryFormatter();
            fmt.Serialize(fs, ds);
            fs.Close();
        }

        public static DataSet BinaryDeserializeToTable(this FileInfo fi)
        {
            FileStream fs = new FileStream(fi.FullName, FileMode.Open);
            BinaryFormatter fmt = new BinaryFormatter();
            object result = fmt.Deserialize(fs);
            return (DataSet)result;
        }

        public static bool HasChanged(this DataTable dataTable)
        {
            foreach (DataRow dr in dataTable.Rows)
                if (dr.RowState != DataRowState.Unchanged)
                {
                    return true;
                }

            return false;
        }

        public static string SerializeChanges(this DataTable dt, params string[] columnNames)
        {
            Dictionary<string, string> cols = new Dictionary<string, string>();

            foreach (DataColumn dc in dt.Columns)
                if (dc.Expression != "")
                {
                    cols.Add(dc.ColumnName, dc.Expression);
                    dc.Expression = "";
                }

            DataTable dtChanges = new DataTable();

            if (dt.DataSet != null)
            {
                DataSet dsChanges = dt.DataSet.GetChanges();
                if (dsChanges != null && dsChanges.Tables[dt.TableName] != null)
                    dtChanges = dsChanges.Tables[dt.TableName];
            }
            else
                dtChanges = dt.GetChanges();

            foreach (KeyValuePair<string, string> col in cols)
                dt.Columns[col.Key].Expression = col.Value;
            if (columnNames.Length == 0)
                return Serialize(dtChanges);
            else
                return Serialize(dtChanges.DefaultView.ToTable(false, columnNames));
        }

        public static string Serialize(this DataTable dataTable)
        {
            DataSet dsNew = new DataSet("MyData");
            DataTable dtNew;
            if (dataTable != null)
                dtNew = dataTable.DefaultView.ToTable();
            else
                dtNew = new DataTable();
            dtNew.TableName = "Table";
            dsNew.Tables.Add(dtNew);

            for (int i = dtNew.Columns.Count - 1; i >= 0; i--)
                if (dtNew.Columns[i].DataType == typeof(System.Byte[]))
                    dtNew.Columns.RemoveAt(i);
                else if (dtNew.Columns[i].DataType == typeof(DateTime))
                    dtNew.Columns[i].DateTimeMode = DataSetDateTime.Unspecified;

            using (TextWriter sw = new StringWriter(CultureInfo.InvariantCulture))
            using (XmlTextWriter tw = new DataManager.KarbelXmlWriter(sw))
            {
                dtNew.WriteXml(tw, XmlWriteMode.IgnoreSchema);
                string oldstr = " xmlns=\"http://tempuri.org/MyData.xsd\"";
                return sw.ToString().Replace(oldstr, string.Empty); // Replace("DocumentElement", "MyData");
            }
        }

        public static void BulkCopy(this DataTable table, string connectionString, string destinationTableName)
        {
            // take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.  

            using (var bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                foreach (DataColumn col in table.Columns)
                    bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);

                bulkCopy.BulkCopyTimeout = 600;
                bulkCopy.DestinationTableName = destinationTableName;
                bulkCopy.WriteToServer(table);
            }
        }
    }
}

